# 全局配置参数
import os
import time
import vcf
import pandas as pd
from sqlalchemy import create_engine, text
from pymysql import connect
from .config import HOST, PORT, USER, PASSWORD, DB, LOGGING_CONFIG
from tqdm import tqdm

# 配置日志记录
import logging.config

logging.config.dictConfig(LOGGING_CONFIG)
logger = logging.getLogger('default')


class CSVBase:
    """
    csv tsv数据库导入基类

    id_field：数据表id字段名
    base_path：必填  要读取的文件夹绝对路径
    file_suffix：要读取的文件后缀，.csv/.tsv,默认 .csv
    file_sep：单元格之间的分隔符
    create_syntax：必填   数据表创建语句
    """
    id_field = 'id'
    base_path = None
    file_suffix = '.csv'
    file_sep = ',' if file_suffix == '.csv' else '\t'
    create_syntax = None

    def __init__(self):
        """
        根据path生成data_sources
        """
        logger.info('初始化导入.....')
        if self.base_path is None or self.create_syntax is None:
            logger.error('base_path和create_syntax初始变量必须配置！')
            raise ValueError('base_path和create_syntax初始变量必须配置！')
        self.df = None
        # 解析路径，生成数据源
        self.data_sources = []
        self._parse_path()

        # 初始化连接
        self.engine = create_engine(f'mysql+pymysql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DB}?charset=utf8')
        logger.info('初始化完成.....')

    def _parse_path(self):
        if os.path.exists(self.base_path):
            file_list = os.listdir(self.base_path)
            for file in file_list:
                abs_path = os.path.join(self.base_path, file)
                split_name = os.path.splitext(file)
                if os.path.isfile(abs_path) and split_name[1].lower() == self.file_suffix:
                    self.data_sources.append({
                        'table': split_name[0].lower()[:64] if len(split_name[0]) > 64 else split_name[0].lower(),
                        'path': abs_path
                    })
        else:
            logger.error('检查输入的path参数是否正确！')
            raise ValueError('检查输入的path参数是否正确！')
        if not self.data_sources:
            logger.error('该路径下不存在csv/tsv文件，请检查！')
            raise ValueError('该路径下不存在csv/tsv文件，请检查！')

    def run(self):
        for data in self.data_sources:
            if not self._check_table_exist(data['table']):
                self._create_table(data['table'])
            else:
                # 当数据库已经存在时清空数据
                self._clear_table(data['table'])
            # 读取csv文件
            self._read(data['path'])

            time.sleep(5)
            # 写入数据库
            self._insert(data['table'])
            logger.info('数据表：{}更新完毕,源文件路径：{}'.format(data['table'], data['path']))
            # print('数据表：{}更新完毕,源文件路径：{}'.format(data['table'], data['path']))

    def _insert(self, table):
        self.df.to_sql(table, self.engine, if_exists='append', index_label=self.id_field)

    def _read(self, path):
        self.df = pd.read_csv(path, sep=self.file_sep)
        self.df.index = self.df.index + 1

    # 查看表是否存在，不存则创建表结构
    def _check_table_exist(self, table):
        sql = "SHOW TABLES LIKE '{}'".format(table)
        with self.engine.connect() as connect:
            try:
                row = connect.execute(text(sql)).one_or_none()
            except Exception as e:
                logger.error('检查数据表：{}失败，{}'.format(table, e))
            else:
                if row:
                    logger.info('数据表：{}已存在'.format(table))
                    # print('表格：{}已存在！'.format(table))
                    return True
        return False

    def _create_table(self, table):
        sql = self.create_syntax.format(table)
        with self.engine.connect() as connect:
            try:
                connect.execute(text(sql))
            except Exception as e:
                logger.error('创建数据表：{}失败，{}'.format(table, e))
            else:
                logger.info('创建数据表：{}成功'.format(table))
                # print('创建：{}表成功！'.format(table))

    def _clear_table(self, table):
        sql = 'truncate table `{}`;'.format(table)
        with self.engine.connect() as conn:
            try:
                conn.execute(text(sql))
            except Exception as e:
                logger.info('清空数据表：{}失败，{}'.format(table, e))
            else:
                logger.info('清空数据表：{}'.format(table))
        # print('清空表：{}！'.format(table))


class VCFBase:
    base_path = None
    id_field = None

    def __init__(self):
        if self.base_path is None or self.id_field is None:
            logger.error('base_path和id_field初始变量必须配置！')
            raise ValueError('base_path和id_field初始变量必须配置！')

        self.conn = connect(host=HOST, port=PORT, user=USER, passwd=PASSWORD, db=DB)
        self.cur = self.conn.cursor()
        # self.conn.begin()
        self.data_sources = []
        self._parse_path()

    def __del__(self):
        # self.conn.commit()
        self.cur.close()
        self.conn.close()

    def run(self):
        for data in self.data_sources:
            if not self._check_table_exist(data['table']):
                self._create_table(data['table'])
            else:
                # 当数据库已经存在时清空数据
                self._clear_table(data['table'])
            # 读取vcf文件
            self._read(data['path'])
            # `CHROM`,`POS`,`ID`,`REF`,`ALT`,`QUAL`,`FILTER`,`INFO`

            data_set = []
            count = 0  # 统计数目
            for index, reader in enumerate(tqdm(self.vcf_reader), start=1):
                count += 1
                data_set.append((reader.CHROM, reader.POS, reader.ID, reader.REF, str(reader.ALT), reader.QUAL, str(reader.FILTER), str(reader.INFO)))
                if index % 10000 == 0:
                    # 写入数据库
                    result = self._insert(data['table'], data_set)
                    if not result:
                        count -= 10000
                    data_set = []
            else:
                result = self._insert(data['table'], data_set)
                if not result:
                    count -= len(data_set)
            logger.info('数据表：{}更新完毕\n源文件路径：{}'.format(data['table'], data['path']))
            logger.info("数据插入完成，成功：{}条".format(count))
            # print('数据表：{}更新完毕\n源文件路径：{}'.format(data['table'], data['path']))
            # print("数据插入完成，成功：{}条".format(count))

    def _read(self, path):
        self.vcf_reader = vcf.VCFReader(open(path))

    def _parse_path(self):
        if os.path.exists(self.base_path):
            file_list = os.listdir(self.base_path)
            for file in file_list:
                abs_path = os.path.join(self.base_path, file)
                split_name = os.path.splitext(file)
                if os.path.isfile(abs_path) and split_name[1].lower() == '.vcf':
                    self.data_sources.append({
                        'table': split_name[0].lower()[:64] if len(split_name[0]) > 64 else split_name[0].lower(),
                        'path': abs_path
                    })
        else:
            logger.error('检查path是否正确！')
            # raise ValueError('检查path是否正确！')
        if not self.data_sources:
            logger.error('该路径下不存在vcf文件，请检查！')
            # raise ValueError('该路径下不存在vcf文件，请检查！')

    def _check_table_exist(self, table):
        sql = "SHOW TABLES LIKE '{}'".format(table)
        try:
            row = self.cur.execute(sql)
        except Exception as e:
            logger.error('检查数据表：{}时出错：{}'.format(table, e))
        else:
            if row:
                logger.info('数据表：{}已存在'.format(table))
                # print('数据表：{}已存在'.format(table))
                return True
        return False

    def _create_table(self, table):
        sql = """
        CREATE TABLE `{}` (
          `{}` bigint(20) NOT NULL AUTO_INCREMENT,
          `CHROM` varchar(255) DEFAULT NULL,
          `POS` varchar(255) DEFAULT NULL,
          `ID` varchar(255) DEFAULT NULL,
          `REF` mediumtext,
          `ALT` mediumtext,
          `QUAL` varchar(255) DEFAULT NULL,
          `FILTER` varchar(255) DEFAULT NULL,
          `INFO` mediumtext,
          PRIMARY KEY (`{}`),
          KEY `pos` (`CHROM`,`POS`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        """.format(table, self.id_field, self.id_field)
        # print(sql)
        try:
            self.cur.execute(sql)
        except Exception as e:
            logger.error('新建数据表:{}失败：{}'.format(table, e))
        else:
            logger.info('新建数据表:{}'.format(table))
            # print('新建数据表{}成功'.format(table))

    def _clear_table(self, table):
        sql = 'truncate table `{}`;'.format(table)
        try:
            self.cur.execute(sql)
        except Exception as e:
            # print(e)
            logger.error('清空数据表：{}失败，{}'.format(table, e))
            self.conn.rollback()
        else:
            self.conn.commit()
            logger.info('清空数据表：{}'.format(table))
            # print('清空数据表：{}'.format(table))

    def _insert(self, table, data_set):
        sql = "INSERT INTO `{}` (`CHROM`,`POS`,`ID`,`REF`,`ALT`,`QUAL`,`FILTER`,`INFO`)VALUES(%s,%s,%s,%s,%s,%s,%s,%s);".format(table)
        # print(data_set)
        # 异常处理
        try:
            self.cur.executemany(sql, data_set)
            # print("insert success")  # 测试语句
        except Exception as e:
            logger.error('插入数据错误：{}'.format(e))
            # print('Insert error:', e)
            self.conn.rollback()
            return False
        else:
            self.conn.commit()
            return True


if __name__ == '__main__':
    pass
    # dbvar_path = r"C:\Users\yang\Desktop\fsdownload\dbvar"
    # dbvar = Dbvar(dbvar_path)
    # dbvar.run()
    # esp = VariomeGME()
    # esp.run()
